path: root/app/api/projects/[projectId]/stats/route.ts

// app/api/fileSystemProjects/[projectId]/stats/route.ts
import { NextRequest, NextResponse } from 'next/server';
import { getServerSession } from 'next-auth/next';
import { authOptions } from '@/app/api/auth/[...nextauth]/route';
import db from "@/db/db";
import { fileItems, fileActivityLogs, fileSystemProjects, projectMembers } from "@/db/schema";
import { eq, and, gte, sql, desc } from "drizzle-orm";

export async function GET(
  request: NextRequest,
  context: { params: Promise<{ projectId: string }> }
) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user) {
      return NextResponse.json({ error: '인증이 필요합니다' }, { status: 401 });
    }

    const params = await context.params;
    const projectId = params.projectId;
    
    // URL 파라미터에서 날짜 범위 가져오기
    const searchParams = request.nextUrl.searchParams;
    const range = searchParams.get('range') || '30d';
    
    // 날짜 범위 계산
    const now = new Date();
    let startDate = new Date();
    
    switch (range) {
      case '7d':
        startDate.setDate(now.getDate() - 7);
        break;
      case '30d':
        startDate.setDate(now.getDate() - 30);
        break;
      case '90d':
        startDate.setDate(now.getDate() - 90);
        break;
      default:
        startDate.setDate(now.getDate() - 30);
    }

    // 이전 기간 (트렌드 계산용)
    const previousStartDate = new Date(startDate);
    previousStartDate.setDate(previousStartDate.getDate() - (now.getTime() - startDate.getTime()) / (1000 * 60 * 60 * 24));

    // 프로젝트 접근 권한 확인
    const projectMember = await db.query.projectMembers.findFirst({
      where: and(
        eq(projectMembers.projectId, projectId),
        eq(projectMembers.userId, Number(session.user.id))
      ),
    });

    const isInternalUser = session.user.domain !== 'partners';
    
    // 내부 사용자가 아니고 프로젝트 멤버가 아닌 경우 접근 거부
    if (!isInternalUser && !projectMember) {
      return NextResponse.json(
        { error: '통계를 볼 권한이 없습니다' },
        { status: 403 }
      );
    }

    // 1. 스토리지 통계
    const storageStats = await db
      .select({
        totalSize: sql<number>`COALESCE(SUM(${fileItems.size}), 0)`,
        fileCount: sql<number>`COUNT(CASE WHEN ${fileItems.type} = 'file' THEN 1 END)`,
        folderCount: sql<number>`COUNT(CASE WHEN ${fileItems.type} = 'folder' THEN 1 END)`,
      })
      .from(fileItems)
      .where(eq(fileItems.projectId, projectId));

    // 카테고리별 파일 수
    const categoryStats = await db
      .select({
        category: fileItems.category,
        count: sql<number>`COUNT(*)`,
      })
      .from(fileItems)
      .where(and(
        eq(fileItems.projectId, projectId),
        eq(fileItems.type, 'file')
      ))
      .groupBy(fileItems.category);

    const byCategory = {
      public: 0,
      restricted: 0,
      confidential: 0,
      internal: 0,
    };

    categoryStats.forEach(stat => {
      if (stat.category && stat.category in byCategory) {
        byCategory[stat.category as keyof typeof byCategory] = Number(stat.count);
      }
    });

    // 2. 활동 통계 (현재 기간)
    const activityStats = await db
      .select({
        action: fileActivityLogs.action,
        count: sql<number>`COUNT(*)`,
      })
      .from(fileActivityLogs)
      .where(and(
        eq(fileActivityLogs.projectId, projectId),
        gte(fileActivityLogs.createdAt, startDate)
      ))
      .groupBy(fileActivityLogs.action);

    // 이전 기간 통계 (트렌드 계산용)
    const previousActivityStats = await db
      .select({
        action: fileActivityLogs.action,
        count: sql<number>`COUNT(*)`,
      })
      .from(fileActivityLogs)
      .where(and(
        eq(fileActivityLogs.projectId, projectId),
        gte(fileActivityLogs.createdAt, previousStartDate),
        sql`${fileActivityLogs.createdAt} < ${startDate}`
      ))
      .groupBy(fileActivityLogs.action);

    const activityCounts = {
      views: 0,
      downloads: 0,
      uploads: 0,
      shares: 0,
    };

    const previousCounts = {
      downloads: 0,
    };

    activityStats.forEach(stat => {
      switch (stat.action) {
        case 'view':
          activityCounts.views = Number(stat.count);
          break;
        case 'download':
          activityCounts.downloads = Number(stat.count);
          break;
        case 'upload':
          activityCounts.uploads = Number(stat.count);
          break;
        case 'share':
          activityCounts.shares = Number(stat.count);
          break;
      }
    });

    previousActivityStats.forEach(stat => {
      if (stat.action === 'download') {
        previousCounts.downloads = Number(stat.count);
      }
    });

    // 트렌드 계산 (다운로드 기준)
    const trend = previousCounts.downloads > 0 
      ? Math.round(((activityCounts.downloads - previousCounts.downloads) / previousCounts.downloads) * 100)
      : 0;

    // 3. 사용자 통계
    const userStats = await db
      .select({
        total: sql<number>`COUNT(DISTINCT ${projectMembers.userId})`,
      })
      .from(projectMembers)
      .where(eq(projectMembers.projectId, projectId));

    // 활성 사용자 (최근 활동이 있는 사용자)
    const activeUsers = await db
      .select({
        count: sql<number>`COUNT(DISTINCT ${fileActivityLogs.userId})`,
      })
      .from(fileActivityLogs)
      .where(and(
        eq(fileActivityLogs.projectId, projectId),
        gte(fileActivityLogs.createdAt, startDate)
      ));

    // 역할별 사용자 수 (간단하게 처리)
    const roleStats = await db
      .select({
        role: projectMembers.role,
        count: sql<number>`COUNT(*)`,
      })
      .from(projectMembers)
      .where(eq(projectMembers.projectId, projectId))
      .groupBy(projectMembers.role);

    const byRole = {
      admin: 0,
      editor: 0,
      viewer: 0,
    };

    roleStats.forEach(stat => {
      if (stat.role === 'owner' || stat.role === 'admin') {
        byRole.admin = Number(stat.count);
      } else if (stat.role === 'editor') {
        byRole.editor = Number(stat.count);
      } else {
        byRole.viewer = Number(stat.count);
      }
    });

    // 4. 최근 활동 내역
    const recentActivities = await db
      .select({
        action: fileActivityLogs.action,
        userEmail: fileActivityLogs.userEmail,
        createdAt: fileActivityLogs.createdAt,
        fileName: fileItems.name,
        fileType: fileItems.type,
      })
      .from(fileActivityLogs)
      .leftJoin(fileItems, eq(fileActivityLogs.fileItemId, fileItems.id))
      .where(and(
        eq(fileActivityLogs.projectId, projectId),
        gte(fileActivityLogs.createdAt, startDate)
      ))
      .orderBy(desc(fileActivityLogs.createdAt))
      .limit(10);

    const recent = recentActivities.map(activity => ({
      type: activity.fileType || 'file',
      user: activity.userEmail?.split('@')[0] || 'Unknown',
      action: activity.action,
      timestamp: activity.createdAt.toISOString(),
      details: activity.fileName || 'Unknown file',
    }));

    // 5. 프로젝트 정보 (스토리지 제한 등)
    const project = await db.query.fileSystemProjects.findFirst({
      where: eq(fileSystemProjects.id, projectId),
    });

    const storageLimit = 10 * 1024 * 1024 * 1024; // 기본 10GB

    // 응답 데이터 구성
    const stats = {
      storage: {
        used: Number(storageStats[0]?.totalSize || 0),
        limit: storageLimit,
        fileCount: Number(storageStats[0]?.fileCount || 0),
        folderCount: Number(storageStats[0]?.folderCount || 0),
        byCategory,
      },
      activity: {
        views: activityCounts.views,
        downloads: activityCounts.downloads,
        uploads: activityCounts.uploads,
        shares: activityCounts.shares,
        trend,
      },
      users: {
        total: Number(userStats[0]?.total || 0),
        active: Number(activeUsers[0]?.count || 0),
        byRole,
      },
      recent,
    };

    return NextResponse.json(stats);

  } catch (error) {
    console.error('통계 조회 오류:', error);
    return NextResponse.json(
      { error: '통계를 불러올 수 없습니다' },
      { status: 500 }
    );
  }
}